﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using Models;
using Common;
namespace DAL
{
   public  class UserDAL
    {
       //展示全部
       public DataTable ListAll(Dictionary<string,string> dic) {
           string sql = string.Empty;
           if(!string.IsNullOrWhiteSpace(dic["username"])){
               sql += " and username like '%" + dic["username"] + "%' ";
           }
           if (!string.IsNullOrWhiteSpace(dic["realname"]))
           {
               sql += " and realname like '%" + dic["realname"] + "%' ";
           }
           if (!string.IsNullOrWhiteSpace(dic["phone"]))
           {
               sql += " and phone like '%" + dic["phone"] + "%' ";
           }
           return SqlHelper.ExecuteDataTable("select * from user where isdel=@isdel "+sql+" order by edittime desc", new MySqlParameter("@isdel", "0"));
       }
       //保存数据
       public int SaveData(Operator o ) {
           string sql = "insert into user(username,realname,phone,password,addTime,editTime,addUser,editUser,isadmin,isdel) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')";
           sql = string.Format(sql,o.Username,o.realname,o.phone,o.Password,o.AddTime,o.EditTime,o.AddUser,o.EditUser,o.IsAdmin,"0");
           return SqlHelper.ExecuteMore(sql);
       }
       //根据用户id查询
       public Operator GetById(string id) {
           string sql = "select * from user where uid =@uid";
           DataTable dt = SqlHelper.ExecuteDataTable(sql,new MySqlParameter("@uid",id));
           if (dt.Rows.Count <= 0) {
               return null;
           }
           else if (dt.Rows.Count > 1) { 
                throw new Exception("存在重复用户");
           }else{
                return ToOperator(dt.Rows[0]);
           }
       }
       //更新
       public int UpdateByInfo(Operator o,bool ps) {
           int result = 0;
           if (ps)
           {
               result = SqlHelper.ExecuteNonQuery("update user set realname=@realname,username=@username,password=@password,phone =@phone,editTime=@editTime,editUser=@editUser,isadmin=@isadmin where uid = @uid",
                   new MySqlParameter("@realname", o.realname), new MySqlParameter("@username", o.Username), new MySqlParameter("@password", o.Password), new MySqlParameter("@phone", o.phone),
                   new MySqlParameter("@editTime", o.EditTime), new MySqlParameter("@editUser", o.EditUser), new MySqlParameter("@isadmin", o.IsAdmin), new MySqlParameter("@uid", o.Uid));
           }
           else {
               result = SqlHelper.ExecuteNonQuery("update user set realname=@realname,username=@username,phone =@phone,editTime=@editTime,editUser=@editUser,isadmin=@isadmin  where uid = @uid",
                   new MySqlParameter("@realname", o.realname), new MySqlParameter("@username", o.Username), new MySqlParameter("@phone", o.phone),
                    new MySqlParameter("@editTime", o.EditTime), new MySqlParameter("@editUser", o.EditUser), new MySqlParameter("@isadmin", o.IsAdmin), new MySqlParameter("@uid", o.Uid));
           }
           return result;
       }
       
       private Operator ToOperator(DataRow dr)
       {
           Operator op = new Operator();
           op.Uid = (int)dr["uid"];
           op.Username = dr["username"].ToString();
           op.Password = dr["password"].ToString();
           op.realname = dr["realname"].ToString();
           op.phone = dr["phone"].ToString();
           op.isdel = dr["isdel"].ToString();
           op.IsAdmin = dr["isadmin"].ToString();
           return op;
       }
       public bool DelById(string uid) {
           int result = SqlHelper.ExecuteNonQuery("update user set isdel=1 ,editTime=@editTime,editUser=@editUser where uid =@uid",
               new MySqlParameter("@editTime", DateTime.Now.ToString()), new MySqlParameter("@editUser", RemeberName.Username), new MySqlParameter("@uid", uid));
           return result > 0 ? true : false;

       }
    }
}
